﻿Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.CompilerServices
Imports System
Imports System.Data.OleDb
Imports System.Reflection
Imports Microsoft.Office.Interop.Excel

Namespace ReportExcel
    Public Class clsReport
        ' Methods
        Public Sub New(ByRef rs1 As OleDbDataReader, ByVal strTemplateFileName1 As String, ByVal strFileName1 As String)
            Me.rs = rs1
            Me.strFileName = strFileName1
            Me.vtGroupList = New Collection
            Me.vtDetailList = New Collection
            Me.hstParameter = New Collection
            Me.vtColToMerge = New Collection
            Me.xlTApp = New Application
            Me.xlTWorkbook = Me.xlTApp.Workbooks.Open(strTemplateFileName1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
            Me.xlTSheet = DirectCast(Me.xlTWorkbook.Sheets._Default(1), Worksheet)
            Me.xlSheetName = Me.xlTSheet.Name
            Me.xlTSheet.Copy(Missing.Value, Me.xlTSheet)
            Me.xlSheet = DirectCast(Me.xlTWorkbook.Sheets._Default(2), Worksheet)
            Me.parseTemplate()
        End Sub

        Public Sub New(ByRef rs1 As OleDbDataReader, ByVal strTemplateFileName1 As String, ByVal strFileName1 As String, ByVal intDetailX1 As Integer, ByVal intDetailY1 As Integer, ByVal intTemplateHeight1 As Integer)
            Me.rs = rs1
            Me.intDetailX = intDetailX1
            Me.intDetailY = intDetailY1
            Me.intTemplateHeight = intTemplateHeight1
            Me.strFileName = strFileName1
            Me.vtGroupList = New Collection
            Me.vtDetailList = New Collection
            Me.xlTApp = New Application
            Me.xlTWorkbook = Me.xlTApp.Workbooks.Open(strTemplateFileName1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
            Me.xlTSheet = DirectCast(Me.xlTWorkbook.Sheets._Default(1), Worksheet)
            Me.xlSheetName = Me.xlTSheet.Name
            Me.xlTSheet.Copy(Missing.Value, Me.xlTSheet)
            Me.xlSheet = DirectCast(Me.xlTWorkbook.Sheets._Default(2), Worksheet)
        End Sub

        Public Sub AddDetail(ByVal intTemplateRow As Integer, ByVal intColCount As Integer)
            Dim detail As New clsDetail(Me.xlTSheet, Me.xlSheet, Me.rs, intTemplateRow, intColCount, Me.intDetailX)
            Me.vtDetailList.Add(detail, Nothing, Nothing, Nothing)
        End Sub

        Public Sub AddGroup(ByVal strGroupFieldName As String, ByVal intTemplateRow As Integer, ByVal intColCount As Integer)
            Dim group As New clsGroup(Me.xlTSheet, Me.xlSheet, Me.rs, strGroupFieldName, intTemplateRow, intColCount, Me.intDetailX)
            Me.vtGroupList.Add(group, Nothing, Nothing, Nothing)
        End Sub

        Public Sub CreateTotal(ByVal intTemplateRow As Integer, ByVal intColCount As Integer)
            Me.total = New clsTotal(Me.xlTSheet, Me.xlSheet, Me.rs, intTemplateRow, intColCount, Me.intDetailX)
        End Sub

        Private Sub DeteteTemplateRow()
            Me.xlSheet.Range((StringType.FromInteger(Me.intDetailY) & ":" & StringType.FromInteger(((Me.intDetailY + Me.intTemplateHeight) - 1))), Missing.Value).EntireRow.Delete(Missing.Value)
        End Sub

        Public Sub fillDataToExcel()
            Try
                Me.DeteteTemplateRow()
                Dim intDetailY As Integer = Me.intDetailY
                Try
                    Do While Me.rs.Read
                        intDetailY = Me.FillGroupData(intDetailY)
                        intDetailY = Me.fillDetailData(intDetailY)
                    Loop
                Catch ex As Exception
                    MessageBox.Show("Do While Me.rs.Read " & ex.Message)
                End Try
                Try
                    If (intDetailY <> Me.intDetailY) Then
                        Dim i As Integer = Me.vtGroupList.Count
                        Do While (i >= 1)
                            Dim group As clsGroup = DirectCast(Me.vtGroupList.Item(i), clsGroup)
                            If (group.intPRow <= group.intCRow) Then
                                group.intPRow = group.intCRow
                                group.intCRow = intDetailY
                            End If
                            group.fillFormula(intDetailY)
                            i = (i + -1)
                        Loop
                    End If
                Catch ex As Exception
                    MessageBox.Show("(intDetailY <> Me.intDetailY) " & ex.Message)
                End Try
                Try
                    If ((Not Me.total Is Nothing) And (intDetailY <> Me.intDetailY)) Then
                        Me.total.fillData(intDetailY)
                    End If
                Catch ex As Exception
                    MessageBox.Show("((Not Me.total Is Nothing) And (intDetailY <> Me.intDetailY)) " & ex.Message)
                End Try
                Try
                    If (((Me.vtColToMerge.Count > 0) And (intDetailY <> Me.intDetailY)) And (Me.orderCol Is Nothing)) Then
                        Me.mergeRowsFollowingCol(intDetailY)
                    End If
                Catch ex As Exception
                    MessageBox.Show("(((Me.vtColToMerge.Count > 0) And (intDetailY <> Me.intDetailY)) " & ex.Message)
                End Try
                Try
                    If (((Not Me.orderCol Is Nothing) And (intDetailY <> Me.intDetailY)) And (Me.vtColToMerge.Count = 0)) Then
                        Me.FillOrderToCol(intDetailY)
                    End If
                Catch ex As Exception
                    MessageBox.Show("(((Not Me.orderCol Is Nothing) And (intDetailY <> Me.intDetailY)) " & ex.Message)
                End Try
                Try
                    If (((Me.vtColToMerge.Count > 0) And (intDetailY <> Me.intDetailY)) And (Not Me.orderCol Is Nothing)) Then
                        Me.MergeRowsFollowingColAndFillOrderToCol(intDetailY)
                    End If
                Catch ex As Exception
                    MessageBox.Show("(((Me.vtColToMerge.Count > 0) And (intDetailY <> Me.intDetailY)) " & ex.Message)
                End Try
                Try
                    LateBinding.LateCall(Me.xlTWorkbook.Sheets._Default(1), Nothing, "Delete", New Object(0 - 1) {}, Nothing, Nothing)
                    Me.xlSheet.Name = Me.xlSheetName
                    Me.xlTWorkbook.SaveAs(Me.strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
                Catch ex As Exception
                    MessageBox.Show("LateBinding.LateCall(Me.xlTWorkbook.Sheets._Default(1) " & ex.Message)
                End Try
            Catch exception1 As Exception
                'kill process excel
                Dim proc As System.Diagnostics.Process

                For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                    proc.Kill()
                Next
                'end kill
                ProjectData.SetProjectError(exception1)
                Dim exception As Exception = exception1
                Interaction.MsgBox(exception.Message, 0, Nothing)
                ProjectData.ClearProjectError()
            Finally
                Me.unInit()
            End Try
        End Sub

        Private Function fillDetailData(ByVal intRow As Integer) As Integer
            Try
                Dim num2 As Integer = Me.vtDetailList.Count
                Dim num4 As Integer = num2
                Dim i As Integer = 1
                Do While (i <= num4)
                    DirectCast(Me.vtDetailList.Item(i), clsDetail).FillDetail(intRow)
                    intRow += 1
                    i += 1
                Loop
                Return intRow
            Catch ex As Exception
                MessageBox.Show("fillDetailData " & ex.Message)
            End Try
        End Function

        Private Function FillGroupData(ByVal intRow As Integer) As Integer
            Dim num2 As Integer = Me.vtGroupList.Count
            Dim num6 As Integer = num2
            Dim i As Integer = 1
            Do While (i <= num6)
                Dim group As clsGroup = DirectCast(Me.vtGroupList.Item(i), clsGroup)
                If group.FillGroup(intRow) Then
                    Dim num5 As Integer = num2
                    Dim j As Integer = (i + 1)
                    Do While (j <= num5)
                        DirectCast(Me.vtGroupList.Item(j), clsGroup).clearPreviousGroupID()
                        j += 1
                    Loop
                    group.fillFormula(intRow)
                    intRow += 1
                End If
                i += 1
            Loop
            Return intRow
        End Function

        Private Sub FillOrderToCol(ByVal intRow As Integer)
            Dim intOrderCol As Integer = Me.orderCol.intOrderCol
            Dim intStart As Integer = Me.orderCol.intStart
            Dim intStep As Integer = Me.orderCol.intStep
            Dim intIsReset As Integer = Me.orderCol.intIsReset
            Dim num3 As Integer = intStart
            Dim num7 As Integer = intRow
            Dim i As Integer = Me.intDetailY
            Do While (i <= num7)
                Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(i, (Me.intColCount + 1)), Range)
                If (StringType.StrCmp(Strings.Trim(StringType.FromObject(range.Value)), "1", False) <> 0) Then
                    If (intIsReset <> 0) Then
                        num3 = intStart
                    End If
                Else
                    LateBinding.LateSetComplex(Me.xlSheet.Cells._Default(i, intOrderCol), Nothing, "Value", New Object() {StringType.FromInteger(num3)}, Nothing, False, True)
                    num3 = (num3 + intStep)
                End If
                i += 1
            Loop
        End Sub

        Private Sub GetColToMergeTemplate(ByVal sheet1 As Worksheet, ByVal intRow As Integer)
            Try
                Dim i As Integer = 2
                Do While (1 <> 0)
                    Dim str As String
                    Dim range As Range = DirectCast(sheet1.Cells._Default(intRow, i), Range)
                    Dim str2 As String = Strings.Trim(StringType.FromObject(range.Value))
                    If (StringType.StrCmp(str2, "", False) = 0) Then
                        Exit Do
                    End If
                    If (range.Comment Is Nothing) Then
                        str = str2
                    Else
                        str = Strings.Trim(("" & range.Comment.Text(Missing.Value, Missing.Value, Missing.Value)))
                    End If
                    Dim num3 As Integer = IntegerType.FromString(str2)
                    Dim num2 As Integer = IntegerType.FromString(str)
                    Dim merge As New clsColToMerge(num3, num2)
                    Me.vtColToMerge.Add(merge, Nothing, Nothing, Nothing)
                    i += 1
                Loop
            Catch ex As Exception
                MessageBox.Show("GetColToMergeTemplate " & ex.Message)
            End Try
        End Sub

        Private Sub GetDetailTemplate(ByRef xlSheet1 As Worksheet, ByVal intRow As Integer)
            Dim num As Integer = IntegerType.FromObject(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 2), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
            Dim num3 As Integer = num
            If BooleanType.FromObject(ObjectType.NotObj((ObjectType.ObjTst(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 3), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing), "", False) = 0))) Then
                num3 = IntegerType.FromObject(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 3), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
            End If
            Dim num4 As Integer = num3
            Dim i As Integer = num
            Do While (i <= num4)
                Dim detail As New clsDetail(Me.xlTSheet, Me.xlSheet, Me.rs, i, Me.intColCount, Me.intDetailX)
                Me.vtDetailList.Add(detail, Nothing, Nothing, Nothing)
                i += 1
            Loop
        End Sub

        Private Sub GetGroupTemplate(ByRef xlSheet1 As Worksheet, ByVal intRow As Integer)
            Try
                Dim num As Integer = IntegerType.FromObject(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 2), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Dim num3 As Integer = IntegerType.FromObject(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 3), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Dim num4 As Integer = num3
                Dim i As Integer = num
                Do While (i <= num4)
                    Dim group As New clsGroup(Me.xlTSheet, Me.xlSheet, Me.rs, i, Me.intColCount, Me.intDetailX)
                    Me.vtGroupList.Add(group, Nothing, Nothing, Nothing)
                    i += 1
                Loop
            Catch ex As Exception
                MessageBox.Show("GetGroupTemplate " & ex.Message)
            End Try
           
        End Sub

        Private Sub GetOrderColTemplate(ByVal sheet1 As Worksheet, ByVal intRow As Integer)
            Dim num3 As Integer = 1
            Dim num4 As Integer = 1
            Dim num As Integer = 0
            Dim range As Range = DirectCast(sheet1.Cells._Default(intRow, 2), Range)
            Dim str2 As String = Strings.Trim(StringType.FromObject(range.Value))
            If (StringType.StrCmp(str2, "", False) <> 0) Then
                Dim num2 As Integer = IntegerType.FromString(str2)
                range = DirectCast(sheet1.Cells._Default(intRow, 3), Range)
                str2 = Strings.Trim(StringType.FromObject(range.Value))
                If ((StringType.StrCmp(str2, "", False) <> 0) And Information.IsNumeric(str2)) Then
                    num3 = IntegerType.FromString(str2)
                End If
                range = DirectCast(sheet1.Cells._Default(intRow, 4), Range)
                str2 = Strings.Trim(StringType.FromObject(range.Value))
                If ((StringType.StrCmp(str2, "", False) <> 0) And Information.IsNumeric(str2)) Then
                    num4 = IntegerType.FromString(str2)
                End If
                range = DirectCast(sheet1.Cells._Default(intRow, 5), Range)
                str2 = Strings.Trim(StringType.FromObject(range.Value))
                If ((StringType.StrCmp(str2, "", False) <> 0) And Information.IsNumeric(str2)) Then
                    num = IntegerType.FromString(str2)
                End If
                Me.orderCol = New clsOrderCol(num2, num3, num4, num)
            End If
        End Sub

        Private Sub GetParameterTemplate(ByRef xlSheet1 As Worksheet, ByVal intRow As Integer)
            Dim i As Integer = 2
            'Dim i As Integer = 10
            Do While (1 <> 0)
                Dim range As Range = DirectCast(xlSheet1.Cells._Default(intRow, i), Range)
                Dim str As String = Strings.Trim(StringType.FromObject(range.Value))
                If (StringType.StrCmp(str, "", False) = 0) Then
                    Exit Do
                End If
                Dim str2 As String = Strings.Trim(range.Comment.Text(Missing.Value, Missing.Value, Missing.Value))
                Me.hstParameter.Add(str2, str, Nothing, Nothing)
                i += 1
            Loop
        End Sub

        Private Sub GetTotalTemplate(ByRef xlSheet1 As Worksheet, ByVal intRow As Integer)
            Dim num As Integer = IntegerType.FromObject(LateBinding.LateGet(xlSheet1.Cells._Default(intRow, 2), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
            Me.total = New clsTotal(Me.xlTSheet, Me.xlSheet, Me.rs, num, Me.intColCount, Me.intDetailX)
        End Sub

        Private Sub mergeRowsFollowingCol(ByVal intRow As Integer)
            Dim num7 As Integer = Me.vtColToMerge.Count
            Dim i As Integer = 1
            Do While (i <= num7)
                Dim merge As clsColToMerge = DirectCast(Me.vtColToMerge.Item(i), clsColToMerge)
                Dim intColToMerge As Integer = merge.intColToMerge
                Dim intColToCompare As Integer = merge.intColToCompare
                Dim str As String = ""
                Dim str2 As String = ""
                Dim intDetailY As Integer = Me.intDetailY
                Dim num6 As Integer = intRow
                Dim j As Integer = Me.intDetailY
                Do While (j <= num6)
                    Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(j, (Me.intColCount + 1)), Range)
                    If (StringType.StrCmp(Strings.Trim(StringType.FromObject(range.Value)), "1", False) <> 0) Then
                        If ((j - intDetailY) > 1) Then
                            Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), (j - 1))
                            Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc((j - 1), intColToMerge)), Missing.Value).MergeCells = True
                        End If
                        intDetailY = j
                        str = ""
                    Else
                        Dim range2 As Range = DirectCast(Me.xlSheet.Cells._Default(j, intColToCompare), Range)
                        str2 = Strings.Trim(StringType.FromObject(range2.Value))
                        If (StringType.StrCmp(str, "", False) = 0) Then
                            str = str2
                            intDetailY = j
                        ElseIf (StringType.StrCmp(Strings.LCase(str), Strings.LCase(str2), False) <> 0) Then
                            If ((j - intDetailY) > 1) Then
                                Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), (j - 1))
                                Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc((j - 1), intColToMerge)), Missing.Value).MergeCells = True
                            End If
                            intDetailY = j
                            str = str2
                        End If
                    End If
                    j += 1
                Loop
                If ((StringType.StrCmp(str, "", False) <> 0) And ((intRow - intDetailY) > 1)) Then
                    Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), intRow)
                    Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc(intRow, intColToMerge)), Missing.Value).MergeCells = True
                End If
                i += 1
            Loop
        End Sub

        Private Sub MergeRowsFollowingColAndFillOrderToCol(ByVal intRow As Integer)
            Dim intOrderCol As Integer = Me.orderCol.intOrderCol
            Dim intStart As Integer = Me.orderCol.intStart
            Dim intStep As Integer = Me.orderCol.intStep
            Dim intIsReset As Integer = Me.orderCol.intIsReset
            Dim num6 As Integer = intStart
            Dim num12 As Integer = Me.vtColToMerge.Count
            Dim i As Integer = 1
            Do While (i <= num12)
                Dim merge As clsColToMerge = DirectCast(Me.vtColToMerge.Item(i), clsColToMerge)
                Dim intColToMerge As Integer = merge.intColToMerge
                Dim intColToCompare As Integer = merge.intColToCompare
                Dim str As String = ""
                Dim str2 As String = ""
                Dim intDetailY As Integer = Me.intDetailY
                Dim num11 As Integer = intRow
                Dim j As Integer = Me.intDetailY
                Do While (j <= num11)
                    Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(j, (Me.intColCount + 1)), Range)
                    If (StringType.StrCmp(Strings.Trim(StringType.FromObject(range.Value)), "1", False) <> 0) Then
                        If ((intIsReset <> 0) And (i = 1)) Then
                            num6 = intStart
                        End If
                        If ((j - intDetailY) > 1) Then
                            Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), (j - 1))
                            Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc((j - 1), intColToMerge)), Missing.Value).MergeCells = True
                        End If
                        intDetailY = j
                        str = ""
                    Else
                        If (i = 1) Then
                            LateBinding.LateSetComplex(Me.xlSheet.Cells._Default(j, intOrderCol), Nothing, "Value", New Object() {StringType.FromInteger(num6)}, Nothing, False, True)
                            num6 = (num6 + intStep)
                        End If
                        Dim range2 As Range = DirectCast(Me.xlSheet.Cells._Default(j, intColToCompare), Range)
                        str2 = Strings.Trim(StringType.FromObject(range2.Value))
                        If (StringType.StrCmp(str, "", False) = 0) Then
                            str = str2
                            intDetailY = j
                        ElseIf (StringType.StrCmp(Strings.LCase(str), Strings.LCase(str2), False) <> 0) Then
                            If ((j - intDetailY) > 1) Then
                                Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), (j - 1))
                                Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc((j - 1), intColToMerge)), Missing.Value).MergeCells = True
                            End If
                            intDetailY = j
                            str = str2
                        End If
                    End If
                    j += 1
                Loop
                If ((StringType.StrCmp(str, "", False) <> 0) And ((intRow - intDetailY) > 1)) Then
                    Me.SetCellsToBlank(intColToMerge, (intDetailY + 1), intRow)
                    Me.xlSheet.Range((Module1.ToLoc(intDetailY, intColToMerge) & ":" & Module1.ToLoc(intColToMerge, intRow)), Missing.Value).MergeCells = True
                End If
                i += 1
            Loop
        End Sub
        Public Sub OpenReport()
            Try
                Me.xlTApp = New Application
                Me.xlTWorkbook = Me.xlTApp.Workbooks.Open(Me.strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
                Me.xlTApp.Visible = True
            Catch exception1 As Exception
                ProjectData.SetProjectError(exception1)
                Dim exception As Exception = exception1
                Interaction.MsgBox(exception.Message, 0, Nothing)
                ProjectData.ClearProjectError()
            Finally
                Me.xlTApp = Nothing
                Me.xlTWorkbook = Nothing
            End Try
        End Sub

        Private Sub parseTemplate()
            If (Me.xlTWorkbook.Sheets.Count > 2) Then
                Dim worksheet As Worksheet = DirectCast(Me.xlTWorkbook.Sheets._Default(3), Worksheet)
                Me.intDetailX = IntegerType.FromObject(LateBinding.LateGet(worksheet.Cells._Default(1, 1), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Me.intDetailY = IntegerType.FromObject(LateBinding.LateGet(worksheet.Cells._Default(1, 2), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Me.intColCount = IntegerType.FromObject(LateBinding.LateGet(worksheet.Cells._Default(1, 3), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Me.intTemplateHeight = IntegerType.FromObject(LateBinding.LateGet(worksheet.Cells._Default(1, 4), Nothing, "Value", New Object(0 - 1) {}, Nothing, Nothing))
                Dim i As Integer = 2
                Do While (1 <> 0)
                    Dim range As Range = DirectCast(worksheet.Cells._Default(i, 1), Range)
                    Dim str As String = Strings.Trim(StringType.FromObject(range.Value))
                    If (StringType.StrCmp(str, "", False) = 0) Then
                        Exit Do
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "group", False) = 0) Then
                        Me.GetGroupTemplate(worksheet, i)
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "detail", False) = 0) Then
                        Me.GetDetailTemplate(worksheet, i)
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "total", False) = 0) Then
                        Me.GetTotalTemplate(worksheet, i)
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "parameter", False) = 0) Then
                        Me.GetParameterTemplate(worksheet, i)
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "coltomerge", False) = 0) Then
                        Me.GetColToMergeTemplate(worksheet, i)
                    End If
                    If (StringType.StrCmp(Strings.LCase(str), "ordercol", False) = 0) Then
                        Me.GetOrderColTemplate(worksheet, i)
                    End If
                    i += 1
                Loop
                Me.xlTApp.DisplayAlerts = False
                LateBinding.LateCall(Me.xlTWorkbook.Sheets._Default(3), Nothing, "Delete", New Object(0 - 1) {}, Nothing, Nothing)
            End If
        End Sub

        Private Sub SetCellsToBlank(ByVal intCol As Integer, ByVal intFromRow As Integer, ByVal intToRow As Integer)
            Dim num2 As Integer = intToRow
            Dim i As Integer = intFromRow
            Do While (i <= num2)
                Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(i, intCol), Range)
                range.Value = ""
                i += 1
            Loop
        End Sub

        Public Function SetParameter(ByVal strLoc As String, ByVal strName As String, ByVal strValue As String) As Object
            Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(strLoc, Missing.Value), Range)
            Dim obj2 As Object = False
            If (Not range Is Nothing) Then
                Dim str As String = Strings.Replace(StringType.FromObject(range.Value), strName, strValue, 1, -1, 0)
                range.Value = str
                obj2 = True
            End If
            Return obj2
        End Function

        Public Function SetParameter1(ByVal strName As String, ByVal strValue As String) As Object
            Dim obj2 As Object = False
            Dim strLoc As String = Strings.Trim(StringType.FromObject(Me.hstParameter.Item(strName)))
            If (StringType.StrCmp(strLoc, "", False) <> 0) Then
                Dim num As Integer
                Dim num2 As Integer
                Module1.ToRowCol(strLoc, num2, num)
                Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(num2, num), Range)
                If (Not range Is Nothing) Then
                    Dim str As String = Strings.Replace(StringType.FromObject(range.Value), strName, strValue, 1, -1, 0)
                    range.Value = str
                    obj2 = True
                End If
            End If
            Return obj2
        End Function

        Private Sub unInit()
            Try
                Dim num As Integer
                num = Me.vtGroupList.Count
                Do While (num >= 1)
                    Dim group As clsGroup = DirectCast(Me.vtGroupList.Item(num), clsGroup)
                    group.unInit()
                    group = Nothing
                    Me.vtGroupList.Remove(num)
                    num = (num + -1)
                Loop
                Me.vtGroupList = Nothing
                Dim num2 As Integer = Me.vtDetailList.Count
                num = 1
                Do While (num <= num2)
                    Dim detail As clsDetail = DirectCast(Me.vtDetailList.Item(num), clsDetail)
                    detail.unInit()
                    detail = Nothing
                    Me.vtDetailList.Remove(num)
                    num += 1
                Loop
                Me.vtDetailList = Nothing
                If (Not Me.total Is Nothing) Then
                    Me.total.unInit()
                    Me.total = Nothing
                End If
                If (Not Me.hstParameter Is Nothing) Then
                    num = Me.hstParameter.Count
                    Do While (num >= 1)
                        Me.hstParameter.Remove(num)
                        num = (num + -1)
                    Loop
                    Me.hstParameter = Nothing
                End If
                Me.vtColToMerge = Nothing
                Me.xlTSheet = Nothing
                Me.xlSheet = Nothing
                Me.xlTWorkbook.Close(Missing.Value, Missing.Value, Missing.Value)
                Me.xlTWorkbook = Nothing
                Me.xlTApp = Nothing
            Catch ex As Exception
                'sinh ra lỗi do killprocess excel
            End Try
        End Sub


        ' Fields
        Private hstParameter As Collection
        Private intColCount As Integer
        Private intDetailX As Integer
        Private intDetailY As Integer
        Private intTemplateHeight As Integer
        Private orderCol As clsOrderCol
        Private rs As OleDbDataReader
        Private strFileName As String
        Private total As clsTotal
        Private vtColToMerge As Collection
        Private vtDetailList As Collection
        Private vtGroupList As Collection
        Private xlSheet As Worksheet
        Private xlSheetName As String
        Private xlTApp As Application
        Private xlTSheet As Worksheet
        Private xlTWorkbook As Workbook
    End Class
End Namespace

